﻿/****** Object:  StoredProcedure [dbo].[USP_GXBZ_EMR_JZLGBL]    Script Date: 2018/12/5 18:44:08 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_GXBZ_EMR_JZLGBL]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[USP_GXBZ_EMR_JZLGBL]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**********暂无数据
[版本号]1.0.0.0.0
[创建时间]2018-12-14
[作者]Z_DD
[版权]卫宁健康科技集团股份有限公司
[描述] 
[功能说明]重庆cdc数据上传--急诊留观病历GXBZ_EMR_JZLGBL数据上传
[返回值]
[调用的USP]
[调用实例] EXEC USP_GXBZ_EMR_JZLGBL
[修改历史]

**********/
CREATE PROC [dbo].[USP_GXBZ_EMR_JZLGBL]    
AS 
  SET XACT_ABORT ON 
DECLARE
	@kssj datetime,					--上传开始时间
	@jssj datetime					--上传结束时间
BEGIN

	IF NOT EXISTS(SELECT table_time FROM SYS_SJSC_TABLE 
	WHERE TABLE_NAME = 'GXBZ_EMR_JZLGBL')
	BEGIN 
		INSERT INTO SYS_SJSC_TABLE(TABLE_NAME,TABLE_TIME)
		VALUES('GXBZ_EMR_JZLGBL','2015-07-01')
	END 
	
	
	--获取已上传的时间
	SELECT @kssj =TABLE_TIME  FROM SYS_SJSC_TABLE(NOLOCK)
	WHERE TABLE_NAME = 'GXBZ_EMR_JZLGBL'
	
	--获取源表中最大上传时间
	SELECT @jssj = MAX(TBRQ) FROM EMR_JZLGBL(NOLOCK)
	
	IF(@jssj>ISNULL(@kssj,'1900-01-01'))
	BEGIN
		BEGIN TRY
		BEGIN TRAN
		
	-------创建临时表  
	CREATE TABLE #TEMP_GXBZ_EMR_JZLGBL(	
		BLLSH                varchar(64)          not null,
		XGBZ                 varchar(1)           not null,
		GRJBXXBSH            varchar(64)          not null,
		CXJMJKDABH           varchar(17)          not null,
		MJZH                 varchar(32)          null,
		DZSQDBH              varchar(20)          null,
		KSMC                 varchar(64)          null,
		YLJGDM               varchar(22)          null,
		YLJGMC               varchar(70)          null,
		JLRQSJ               datetime             null,
		KFSSGH               varchar(64)          null,
		KFYSXM               varchar(30)          null,
		WDGLJGDM             varchar(22)          null,
		WDGLJGMC             varchar(70)          null,
		YSGH                 varchar(64)          null,
		YSXM                 varchar(30)          null,
		GMSBZ                varchar(1)           null,
		GMS                  char(1024)           null,
		ZS                   varchar(1024)        null,
		XBS                  varchar(1024)        null,
		JWS                  varchar(1024)        null,
		TGJC                 varchar(1024)        null,
		FZJCJG               varchar(1024)        null,
		CZBZDM               varchar(1)           null,
		SZGCJG               varchar(1024)        null,
		XYZDMC               varchar(100)         null,
		XYZDDM               varchar(10)          null,
		ZYBMMC               varchar(100)         null,
		ZYBMDM               varchar(10)          null,
		ZYZHMC               varchar(100)         null,
		ZYZHDM               varchar(10)          null,
		BZYJ                 varchar(100)         null,
		ZZZF                 varchar(100)         null,
		YZXMLXDM             varchar(2)           null,
		YZJHKSSJ             datetime             null,
		YZJHJSSJ             datetime             null,
		YZNR                 varchar(1024)        null,
		YZZXSJ               datetime             null,
		ZXRGH                varchar(64)          null,
		ZXRXM                varchar(30)          null,
		ZXKSMC               varchar(32)          null,
		YZKLSJ               datetime             null,
		YZKLRGH              varchar(64)          null,
		YZKLRXM              varchar(30)          null,
		YZKLKSMC             varchar(64)          null,
		SHSJ                 datetime             null,
		SHRGH                varchar(30)          null,
		YZZZSJ               datetime             null,
		TZYZZGH              varchar(64)          null,
		TZYZZXM              varchar(30)          null,
		BZ                   varchar(100)         null,
		YZZXZT               varchar(50)          null,
		SSCZBM               varchar(50)          null,
		SSCZMC               varchar(64)          null,
		SSCZBWMC             varchar(50)          null,
		JRWMC                varchar(100)         null,
		CZFFMS               varchar(1024)        null,
		CZCS                 decimal(3)           null,
		QJKSRQSJ             datetime             null,
		QJJSRQSJ             datetime             null,
		JZQJJL               varchar(1024)        null,
		CJQJRYMD             varchar(200)         null,
		ZYJSZWLBDM           varchar(30)          null,
		SRGCSRQSJ            datetime             null,
		JZLGBCJL             varchar(1024)        null,
		HZQXDM               varchar(1)           null,
		ZYSX                 varchar(1024)        null,
		MJ                   varchar(16)          not null,
		TBRQ                 datetime             not null,
		SCZT                 varchar(1)           not null,
		CS_JYBZ              int                  not null,
		SCSJ                 datetime             not null
	) ON [PRIMARY]
	
	
	--插业务数据到临时表
	INSERT INTO #TEMP_GXBZ_EMR_JZLGBL (
		BLLSH,
		XGBZ,
		GRJBXXBSH,
		CXJMJKDABH,
		MJZH,
		DZSQDBH,
		KSMC,
		YLJGDM,
		YLJGMC,
		JLRQSJ,
		KFSSGH,
		KFYSXM,
		WDGLJGDM,
		WDGLJGMC,
		YSGH,
		YSXM,
		GMSBZ,
		GMS,
		ZS,
		XBS,
		JWS,
		TGJC,
		FZJCJG,
		CZBZDM,
		SZGCJG,
		XYZDMC,
		XYZDDM,
		ZYBMMC,
		ZYBMDM,
		ZYZHMC,
		ZYZHDM,
		BZYJ,
		ZZZF,
		YZXMLXDM,
		YZJHKSSJ,
		YZJHJSSJ,
		YZNR,
		YZZXSJ,
		ZXRGH,
		ZXRXM,
		ZXKSMC,
		YZKLSJ,
		YZKLRGH,
		YZKLRXM,
		YZKLKSMC,
		SHSJ,
		SHRGH,
		YZZZSJ,
		TZYZZGH,
		TZYZZXM,
		BZ,
		YZZXZT,
		SSCZBM,
		SSCZMC,
		SSCZBWMC,
		JRWMC,
		CZFFMS,
		CZCS,
		QJKSRQSJ,
		QJJSRQSJ,
		JZQJJL,
		CJQJRYMD,
		ZYJSZWLBDM,
		SRGCSRQSJ,
		JZLGBCJL,
		HZQXDM,
		ZYSX,
		MJ,
		TBRQ,
		SCZT,
		CS_JYBZ,
		SCSJ
	)
	SELECT
	A.JZLSH  BLLSH,
	A.XGBZ XGBZ
	FROM EMR_JZLGBL A(NOLOCK)
	LEFT JOIN MZJZJLB B ON A.JZLSH =B.JZLSH